The Ivy University Chemistry department wants to organize their materials and experiments. We are to develop an integrated database application that will keep track of chemicals, vendors for the chemicals, orders of the chemicals, equipment, and experiments.
Functionality provided should include:
· User friendly interface(s) for data input for each kind of data.
· User friendly interfaces for update/delete for any kinds of data subject to frequent updates. For instance, when a new shipment of chemicals arrives, it should be easy to update the inventories of the chemicals.
· On-screen buttons leading users to the capabilities that they can make use of
· Support for some pre-planned reports and queries. An initial specification of desirable queries/ reports is given below.
We will talk more about this project in upcoming weeks. Your first task is to understand the requirements in order to begin data modeling. Note that the requirements are deliberately “under-specified” (vague) in order to give a “real-world” feel to the project. Note that the DB design is due Oct 18, the first installment on development is due Nov 11, and the completed project is Due Dec 6. The will be a handout later on project deliverables.
Tentative
Queries / Reports:
For Lab Coordinator:
a) Show all chemicals that should be re-ordered (running low).
b) Show all chemical inventory for chemicals stocked by a given vendor (vendor X).
c) Show all vendors that we sometimes use to obtain a given chemical (chemical X)
d) Show all experiments that use a given chemical (chemical X)
e) Show all experiments that use a given piece of equipment (equipment X)
Reports
a) Show all orders for chemicals that have not yet arrived.
a) Show all chemicals (with quantities) needed for a given experiment (experiment X)
b) Show all equipment needed for a given experiment (experiment X), along with their storage locations.
For Teaching Assistants:
a) Show all chemicals (with quantities) needed for a given experiment (experiment X)
b) Show all equipment needed for a given experiment (experiment X), along with their storage locations.
Always remember when designing output that these are designed to help humans. Thus, for example, if the primary key value is not meaningful to humans, that other identifying info should be displayed in addition/instead. E.g. In addition to or instead of Equipment ID, display equipment name.
Many of these should be “parameterized” in order to be flexible. Queries that should be parameterized are generally indicated with X,Y,Z etc.
These queries/ reports are tentative. If you find any of these that are problematic, check with me. Also, if you think of any great additional canned queries/reports, feel free to include them.